/**
 * <p>Copyright (c) 2010 by DMR LLC. </p>
 */

package com.responsemeasure.db;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

import com.responsemeasure.to.Action;
import com.responsemeasure.to.NineOfFourReportTO;
import com.responsemeasure.to.User;

/**
 * <p>
 * <b>CLASS:</b> com.android.drm.db.DataHelper
 * </p>
 * 
 * ...........................................................................
 * <p>
 * <b>DESCRIPTION:</b><br/>
 * This class provides CRUD operations of database tables.
 * </p>
 * 
 * ...........................................................................
 * <p>
 * <b>MODIFICATION HISTORY:</b>
 * <p>
 * [2010/07/29] emily gu: Initial version.
 * [2010/08/25] michellem: Added method for getting correct hit counts.
 * </p>
 * </p>
 * 
 * ...........................................................................
 * 
 * @author emily gu
 * @since 1.0
 */

public class DataHelper {
	private static final String DATABASE_NAME = "drm.client.db";
	private static final int DATABASE_VERSION = 1;

	private static final String TABLE_USER = "user";
	private static final String TABLE_ACTION = "action";
	private static final String TABLE_TEST = "test";
	private static final String TABLE_TEST_RESPONSE = "test_response";

	private Context context;
	private SQLiteDatabase db;

	private SQLiteStatement insertUserStmt;
	private SQLiteStatement insertActionStmt;
	private SQLiteStatement insertTestStmt;
	private SQLiteStatement insertTestResponseStmt;
	
	private static final String INSERT_USER = " insert into " + TABLE_USER
			+ " (user_name, age) values (?,?)";
	private static final String INSERT_ACTION = " insert into " + TABLE_ACTION
			+ " (name) values (?)";
	private static final String INSERT_TEST = "insert into " + TABLE_TEST
			+ "(user_id, action_id) values (?,?)";
	private static final String INSERT_TEST_RESPONSE = "insert into "
			+ TABLE_TEST_RESPONSE + "(test_id, time) values (?,?)";

	public DataHelper(Context context) {
		this.context = context;
		OpenHelper openHelper = new OpenHelper(this.context);
		this.db = openHelper.getWritableDatabase();

		this.insertUserStmt = this.db.compileStatement(INSERT_USER);
		this.insertActionStmt = this.db.compileStatement(INSERT_ACTION);
		this.insertTestStmt = this.db.compileStatement(INSERT_TEST);
		this.insertTestResponseStmt = this.db
				.compileStatement(INSERT_TEST_RESPONSE);
	}

	public SQLiteDatabase getDb() {
		return this.db;
	}

	public long insertUser(String userName, Long age) {
		this.insertUserStmt.bindString(1, userName);
		this.insertUserStmt.bindLong(2, age);
		return this.insertUserStmt.executeInsert();
	}

	public long insertAction(String name) {
		this.insertActionStmt.bindString(1, name);
		return this.insertActionStmt.executeInsert();
	}

	public long insertTest(long userId, long actionId) {
		this.insertTestStmt.bindLong(1, userId);
		this.insertTestStmt.bindLong(2, actionId);
		return this.insertTestStmt.executeInsert();
	}

	public void updateTest(double averageInterval, long totalInterval,
			int correctHit, int wrongHit, long id) {
		String sql = " UPDATE " + TABLE_TEST + " SET " + " average_interval = "
				+ averageInterval + " ,total_interval = " + totalInterval
				+ " ,correct_hit = " + correctHit + " ,wrong_hit = " + wrongHit
				+ " where id = " + id;

		db.execSQL(sql);
	}

	public long insertResponse(long testId, double timeInterval) {
		this.insertTestResponseStmt.bindLong(1, testId);
		this.insertTestResponseStmt.bindDouble(2, timeInterval);
		return this.insertTestResponseStmt.executeInsert();
	}

	public NineOfFourReportTO getAverageIntervals(long userId, long actionId,
			String userName, String actionName) {
		Cursor cursor = this.db
				.rawQuery(
						" Select average_interval, total_interval, start_time from test where user_id ="
								+ userId + " and action_id = 1", null);

		List<Double> averages = new ArrayList<Double>();
		List<Long> totals = new ArrayList<Long>();
		List<Long> startTimeList = new ArrayList<Long>();

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		long time = 0;
		if (cursor.moveToFirst()) {
			do {
				averages.add(cursor.getDouble(0));
				totals.add(cursor.getLong(1));
				try {
					time = sdf.parse(cursor.getString(2)).getTime();
					startTimeList.add(new Long(time));
				} catch (ParseException e) {
				}
			} while (cursor.moveToNext());
		}

		if (cursor != null) {
			cursor.close();
		}

		NineOfFourReportTO reportTo = new NineOfFourReportTO(averages.size(),
				userName, actionName);
		reportTo.setAverages(averages);
		reportTo.setTotals(totals);
		reportTo.setStartTime(startTimeList);

		return reportTo;
	}
	
	public NineOfFourReportTO getHitCounts(long userId, long actionId,
			String userName, String actionName) {
		Cursor cursor = this.db
				.rawQuery(
						" Select correct_hit from test where user_id ="
								+ userId + " and action_id = 1", null);

		List<Integer> correctHits = new ArrayList<Integer>();

		if (cursor.moveToFirst()) {
			do {
				correctHits.add(cursor.getInt(0));
			} while (cursor.moveToNext());
		}

		if (cursor != null) {
			cursor.close();
		}

		NineOfFourReportTO reportTo = new NineOfFourReportTO(correctHits.size(),
				userName, actionName);
		reportTo.setCorrecHits(correctHits);

		return reportTo;
	}
 
	public long deleteUser(String userName) {
		Cursor cursor = this.db.rawQuery(
				" Select id from user where user_name = '" + userName + "'",
				null);
		long userId = 0;

		if (cursor.moveToFirst()) {
			do {
				userId = cursor.getLong(0);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}

		db
				.execSQL(" DELETE FROM test_response WHERE test_id in (SELECT test.id from test, user where user_name = '"
						+ userName + "'" + " and test.user_id = user.id )");
		db.execSQL(" DELETE FROM test where user_id = " + userId);
		db.execSQL(" DELETE FROM user where user_name = '" + userName + "'");

		return userId;
	}

	public Cursor getUsers() {
		return this.db.rawQuery(
				"Select id as _id, user_name from user order by user_name",
				null);
	}

	public List<User> selectUsers() {
		List<User> list = new ArrayList<User>();
		Cursor cursor = this.db
				.rawQuery(
						" Select id, age, user_name from user order by user_name",
						null);

		if (cursor.moveToFirst()) {
			do {
				User user = new User(cursor.getInt(1), cursor.getString(2));
				user.setId(cursor.getLong(0));
				list.add(user);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		return list;
	}

	public List<Action> selectActions() {
		List<Action> list = new ArrayList<Action>();
		Cursor cursor = this.db.rawQuery(" Select id, name from "
				+ TABLE_ACTION + " order by name", null);

		if (cursor.moveToFirst()) {
			do {
				Action action = new Action(cursor.getLong(0), cursor
						.getString(1));
				list.add(action);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		return list;
	}

	private static class OpenHelper extends SQLiteOpenHelper {

		OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db
					.execSQL("CREATE TABLE "
							+ TABLE_USER
							+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, user_name TEXT NOT NULL UNIQUE, age INTEGER) ");
			db
					.execSQL("CREATE TABLE "
							+ TABLE_ACTION
							+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE) ");
			db
					.execSQL("CREATE TABLE "
							+ TABLE_TEST
							+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, action_id INTEGER,"
							+ "  start_time REAL, average_interval REAL, total_interval REAL, correct_hit INTEGER, wrong_hit INTEGER)");
			db
					.execSQL("CREATE TABLE "
							+ TABLE_TEST_RESPONSE
							+ " (id INTEGER PRIMARY KEY AUTOINCREMENT, test_id INTEGER,"
							+ "  time REAL)");
			db
					.execSQL(" CREATE TRIGGER insert_test_start_time AFTER INSERT ON test"
							+ " BEGIN "
							+ " UPDATE test SET start_time = DATETIME('NOW')  WHERE rowid = new.rowid; "
							+ " END;");
			db.execSQL(" INSERT INTO ACTION (name) values ('Four Of Nine') ");
			db.execSQL(" INSERT INTO ACTION (name) values ('Report') ");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w("DRM",
					"Upgrading database, this will drop tables and recreate.");
			// db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEST_RESPONSE);
			// db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEST);
			//	
			// onCreate(db);
		}
	}
}
